<?php
class Teleraport extends Controller {

    private $aPost;
    private $aUser;

    function Teleraport() {
        parent::Controller();
        $this->load->model('model_telequestion');
        $this->load->library("xmlexcel");

        //verificare sesiune
        if (!$this->usersession->userLoggedIn()) {
            //$aResponse = array("error" => true, "description" => "Sesiunea a expirat", "type" => "nosession");
            //die(json_encode($aResponse));
        }
        copyPost($this->aPost);
        $this->aUser = $this->usersession->currentUser();
    }

    function reportTele() {

        //$this->aPost["client_id"] = 730;
        $this->db->where("ts_active", 1);
        $this->db->where("client_id", $this->aPost["client_id"]);
        $this->db->order_by("ts_prog DESC, ts_interest DESC, ts_rev ASC");
        $aStatus = $this->db->get("tele_status")->result_array();


        //numar contacte client
        $sql = "
        SELECT count(*)as total
        FROM client_persons
        WHERE client_id = {$this->aPost["client_id"]}
        ";
        $sTotalContacte = $this->db->query($sql)->row()->total;

        //log general
        $sql = "
            SELECT DATE(tl_date) as date_log ,tele_log.*, COUNT(tele_log.tl_value)  as cnt , tele_status.* from tele_log
            INNER JOIN (
                            SELECT max(tl_id) as tl_id
                            FROM tele_log
                            INNER JOIN client_persons on client_persons.client_persons_id=tele_log.client_persons_id and client_id={$this->aPost["client_id"]} and tele_log.tl_operation = 'status_mod'
                            INNER JOIN tele_status on tele_status.ts_id = tl_value and ts_active = 1
                            group by DATE(tele_log.tl_date),tele_log.client_persons_id
            ) T on T.tl_id = tele_log.tl_id
            INNER JOIN tele_status on tele_status.ts_id = tele_log.tl_value
            group by DATE(tele_log.tl_date),tele_log.tl_value
            order by DATE(tl_date) asc
        ";
        $aLog = $this->db->query($sql)->result_array();

        //zile distincte din log
        $sqlDays = "
            SELECT DISTINCT(DATE(tl_date)) as date_log
				FROM tele_log
				INNER JOIN client_persons on client_persons.client_persons_id=tele_log.client_persons_id and client_id= {$this->aPost["client_id"]} and tele_log.tl_operation = 'status_mod'
				INNER JOIN tele_status on tele_status.ts_id = tl_value and ts_active = 1
				ORDER BY 	DATE(tl_date)
        ";
        $aLogDays = $this->db->query($sqlDays)->result_array();
        header("Content-type: application/vnd.ms-excel;charset=utf-8");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Content-disposition:  attachment; filename=raport.xls ");
        $newexcel = new Xmlexcel;
        $newexcel->write_header();
        $newexcel->write_document_properties('MTP');
        $newexcel->write_styles();
        $newexcel->start_worksheet("Sumar");


        //setare lungime coloane
        for ($i = 0; $i <= count($aStatus) + 5; $i++) {
            $sCell .= '<Column ss:Width="100"/>';
        }
        $newexcel->write_row($sCell);

        $sCell = "";
        foreach ($aStatus as $aRecStatus) {
            $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aRecStatus["ts_name"] . '</Data></Cell>';
        }
        $newexcel->write_row('
        <Row>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Data contactarii</Data></Cell> ' . $sCell . '
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Prospecti contactati/zi</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Prospecti chestionati /zi</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Total prospecti contactati/chestionati</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Total prospecti ce urmeaza a fi contactati</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Total Prospecti</Data></Cell>
        </Row>');

        foreach ($aLogDays as $key => $aRecDay) {
            $sCell = "";
            $aTotalContactatiZi = $aTotalChestionatiZi = 0;
            $sCell .= '<Cell ss:StyleID="teleLogSumarTxt"><Data ss:Type="String">' . $aRecDay["date_log"] . '</Data></Cell>';

            foreach ($aStatus as $keyStatus => $aRecStatus) {
                $iTotalPerStatus = $this->getStatusZi($aLog, $aRecDay["date_log"], $aRecStatus["ts_id"]);

                //total dupa status
                $aTotalPerStatus[$aRecStatus["ts_id"]] += $iTotalPerStatus;

                //total contactati ( fara programari)
                //if ($aRecStatus["ts_prog"] == 0)
                {
                    $aTotalContactatiZi += $iTotalPerStatus;
                    $aTotalContactati += $iTotalPerStatus;
                }
                //total interesati
                if ($aRecStatus["ts_interest"] == 1 /*&& $aRecStatus["ts_prog"] == 0*/) {
                    $aTotalInteresati += $iTotalPerStatus;
                }

                //total chestionati ( fara programari , fara de revenit )
                if (/*$aRecStatus["ts_prog"] == 0 &&*/ $aRecStatus["ts_rev"] == 0) {
                    $aTotalChestionatiZi += $iTotalPerStatus;
                    $aTotalChestionati += $iTotalPerStatus;
                }
                $sCell .= '<Cell ss:StyleID="teleLogSumarTxt"><Data ss:Type="String">' . $iTotalPerStatus . '</Data></Cell>';
            }

            $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalContactatiZi . '</Data></Cell>';
            $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalChestionatiZi . '</Data></Cell>';
            $newexcel->write_row('  <Row>' . $sCell . '</Row>');
        }

        $sCell = "";
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Total</Data></Cell>';
        foreach ($aStatus as $keyStatus => $aRecStatus) {
            $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalPerStatus[$aRecStatus["ts_id"]] . '</Data></Cell>';
        }

       $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalContactati . '</Data></Cell>';
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalChestionati . '</Data></Cell>';
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalChestionati . '</Data></Cell>';
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . ($sTotalContacte - $aTotalChestionati) . '</Data></Cell>';
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $sTotalContacte . '</Data></Cell>';
       $newexcel->write_row('  <Row>' . $sCell . '</Row>');


        $sCell = "";
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Total Procentual</Data></Cell>';
        foreach ($aStatus as $keyStatus => $aRecStatus) {
            $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . round(($aTotalPerStatus[$aRecStatus["ts_id"]] / $sTotalContacte) * 100, 2) . '%</Data></Cell>';
        }
        $newexcel->write_row('  <Row>' . $sCell . '</Row>');

        $sCell = "";
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Total Interesati</Data></Cell>';
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aTotalInteresati . '</Data></Cell>';
        $sCell .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . round(($aTotalInteresati / $sTotalContacte * 100), 2) . '%</Data></Cell>';
        $newexcel->write_row('  <Row>' . $sCell . '</Row>');

        $newexcel->end_worksheet();
        $newexcel->write_footer();


        foreach ($aLogDays as $aDay) {
            $aDayLog .= $this->getLogByDay($this->aPost["client_id"], $aDay["date_log"]);
        }

        print  $newexcel->header . $newexcel->docprop . $newexcel->styles .
                $this->getChestionar($this->aPost["client_id"]) .
                $newexcel->ws . $newexcel->rows . $newexcel->we .
                $this->exportSched($this->aPost["client_id"]) .
                $this->getLogByDay($this->aPost["client_id"], $aLogDays).
                $newexcel->footer;
        exit;
    }


	function getChestionar($iClient) {

		$customs["age"] = $this->config->item("ageContact");
		$customs["income"] = $this->config->item("incomeContact");
		$customs["employees"] = $this->config->item("employeeContact");
		$customs["available"] = $this->config->item("availableContact");
		$customs["priority"] = $this->config->item("priorityContact");
		$customs["sex"] = $this->config->item("sexContact");
		$customs["turnover"] = $this->config->item("turnoverContact");

		$newexcel = new Xmlexcel;
		$newexcel->start_worksheet("Chestionar");

		$this->benchmark->mark('beg1');

		//zile distincte din log
		$sqlDays = "
            SELECT DISTINCT(DATE(tl_date)) as date_log
				FROM tele_log
				INNER JOIN client_persons on client_persons.client_persons_id=tele_log.client_persons_id and client_id={$iClient} and tele_log.tl_operation = 'status_mod'
				INNER JOIN tele_status on tele_status.ts_id = tl_value and ts_active = 1
				ORDER BY 	DATE(tl_date)
        ";
		$aLogDays = $this->db->query($sqlDays)->result_array();

		$sCellDay = "";
		foreach ($aLogDays as $aLogDaysRec) {
			$sCellDay .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aLogDaysRec["date_log"] . '</Data></Cell>';
		}

		//intrebari
		$sqQuestion = "
            SELECT *
				FROM tele_question
				where client_id={$iClient} and tq_active = 1
				ORDER BY tq_name
        ";
		$aLogQuestion = $this->db->query($sqQuestion)->result_array();
		$sCellQuestion = "";
		foreach ($aLogQuestion as $aLogQuestionRec) {
			$sCellQuestion .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aLogQuestionRec["tq_name"] . '</Data></Cell>';
		}

		//custom
		$sqCustoms = "
            SELECT *
				FROM tele_custom
				where client_id={$iClient} and tc_active = 1
				ORDER BY tc_name
        ";
		$aCustom = $this->db->query($sqCustoms)->result_array();
		$sCellCustom = "";
		foreach ($aCustom as $aCustomRec) {
			$sCellCustom .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aCustomRec["tc_name"] . '</Data></Cell>';
		}
		for ($i = 0; $i <= 100; $i++) {
			$sColumn .= '<Column ss:Width="80"/>';
		}


		//custom
		$sqQuestionFre = "
            SELECT *
				FROM tele_questionfree
				WHERE client_id={$iClient} and tqf_active = 1
				ORDER BY tqf_name
        ";
		$aQuestionFree = $this->db->query($sqQuestionFre)->result_array();
		$sCellQuestFree = "";
		foreach ($aQuestionFree as $sCellQuestFreeRec) {
			$sCellQuestFree .= '<Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $sCellQuestFreeRec["tqf_name"] . '</Data></Cell>';
		}



		for ($i = 0; $i <= 100; $i++) {
			$sColumn .= '<Column ss:Width="80"/>';
		}
		$newexcel->write_row($sColumn);

		$newexcel->write_row('
         <Row  ss:Height="20">
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nr Crt</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Compania</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nume</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Prenume</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Functia</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Oras</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Judet</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Status</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Alte Observatii</Data></Cell>' .
			$sCellCustom.
			$sCellQuestion . $sCellQuestFree.
			$sCellDay . '
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Compania</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nume</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Prenume</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Sex</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Domeniu</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Subdomeniu</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Functia</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Subfunctia</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Departament</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Adresa</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Cod postal</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Oras</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Judet</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Telefon</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Mobil</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Email</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Email 2</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Web</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Zi nastere</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Grupa varsta</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Grupa venit</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Cifra afaceri</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nr angajati</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Cod inregistrare</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">User</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">ID Baza Date</Data></Cell>

         </Row>');




		$sSql = " SELECT tele_obs, client_persons_id, ts_name, tele_obs,`domain`.name as domain_name,   `SD`.name as subdomain_name,
              division.name as division_name, persons.address, persons.address2, birth, age, income, turnover, employees, cui, ts_contact_data, user.name as user_name,client_persons.person_id,

            IF(extern =1 , client_persons.company , persons.company) as company,
            IF(extern =1 , client_persons.lname , persons.lname) as lname,
            IF(extern =1 , client_persons.fname , persons.fname) as fname,
            IF(extern =1 , CP.name , P.name) as position_name,
            IF(extern =1 , CSP.name , SP.name) as subposition_name,
            IF(extern =0 , persons.phone , client_persons.phone) as phone,
            IF(extern =0 , persons.mobil , client_persons.mobil) as mobil,
            IF(extern =1 , client_persons.email , persons.email) as email,
            IF(extern =1 , client_persons.email2 , persons.email2) as email2,
            IF(extern =1 , CC.name , city.name) as city_id_val,
            IF(extern =1 , client_persons.city_id , persons.city_id) as city_id,
            IF(extern =1 , CCo.name , county.name) as county_id_val,
            IF(extern =1 , client_persons.sex , persons.sex) as sex,
            IF(extern =1 , client_persons.web , persons.web) as web
            from client_persons
                left join persons on persons.person_id = client_persons.person_id
                left join position P on P.position_id=persons.position_id
                left join position CP on CP.position_id=client_persons.position_id
                left join position SP on SP.position_id=persons.position_sub_id
                left join position CSP on CSP.position_id=client_persons.position_sub_id
                left join city  on city.city_id=persons.city_id
                left join county  on county.county_id=persons.county_id
                left join city CC  on CC.city_id=client_persons.city_id
                left join county CCo on CCo.county_id=client_persons.county_id
                left join tele_status  on tele_status.ts_id=client_persons.ts_id
                left join `domain`  on `domain`.domain_id=persons.domain_id
                left join `domain` SD  on `SD`.domain_id=persons.domain_sub_id
                left join `division`   on `division`.division_id=persons.division_id
                left join `user`   on `user`.user_id=client_persons.tele_user
			where client_persons.client_id = {$iClient}
            order by company asc , lname asc ";
		$aContact = $this->db->query($sSql)->result_array();




		$sSQl = "
                SELECT DATE(tl_date) as date_log, tele_log.client_persons_id,  tele_status.ts_name, tele_obs
                from tele_log
                INNER JOIN (
                    SELECT max(tl_id) as tl_id
                    FROM tele_log
                    INNER JOIN client_persons on client_persons.client_persons_id=tele_log.client_persons_id and client_id={$iClient} and tele_log.tl_operation = 'status_mod'
                    INNER JOIN tele_status on tele_status.ts_id = tl_value and ts_active = 1
                    group by tele_log.client_persons_id, tele_log.tl_value
                ) T on T.tl_id = tele_log.tl_id
                INNER JOIN tele_status on tele_status.ts_id = tele_log.tl_value
                left join client_persons on client_persons.client_persons_id = tele_log.client_persons_id
                order by date_log asc";
		$aLogStatusContactTemp = $this->db->query($sSQl)->result_array();

		foreach($aLogStatusContactTemp as $aRec) {
			$aLogStatusContact[$aRec["client_persons_id"]."_".$aRec["date_log"]] = $aRec;
		}
		unset($aLogStatusContactTemp);

		$sSQl = "
                SELECT client_persons_id, tele_answer.tq_id, tele_answer.tqa_id, tqa_name, tq_name
                FROM tele_answer
                INNER JOIN tele_question on tele_answer.tq_id=tele_question.tq_id  and tele_question.tq_active = 1
                LEFT JOIN tele_question_answer on tele_question_answer.tqa_id=tele_answer.tqa_id
                WHERE tele_answer.client_id ={$iClient} ";
		$aAnswerContactTemp = $this->db->query($sSQl)->result_array();

		foreach($aAnswerContactTemp as $aRec) {
			$aAnswerContact[$aRec["client_persons_id"]."_".$aRec["tq_id"]] =$aRec ;
		}


		//raspunsuri custom
		$sSQl = "
                SELECT client_persons_id, tele_custom.tc_id, tele_custom_answer.tca_id, tca_answer , tc_name
                FROM tele_custom_answer
                INNER JOIN tele_custom on tele_custom.tc_id=tele_custom_answer.tc_id  and tele_custom.tc_active = 1
                WHERE tele_custom_answer.client_id ={$iClient} ";
		$aCustomAnswer = $this->db->query($sSQl)->result_array();


		//raspunsuri free
		$sSQl = "
                SELECT client_persons_id, tele_questionfree.tqf_id,  taf_answer
                FROM tele_answerfree
                INNER JOIN tele_questionfree on tele_questionfree.tqf_id=tele_answerfree.tqf_id  and tele_questionfree.tqf_active = 1
                WHERE tele_answerfree.client_id ={$iClient} ";
		$aAnswerFree = $this->db->query($sSQl)->result_array();


		foreach ($aContact as $key => $aContactRec) {

			$sCellDayAnswer = "";
			foreach ($aLogQuestion as $aLogQuestionRec) {
				$sQuestion = "";

				if(isset($aAnswerContact[$aContactRec["client_persons_id"]."_".$aLogQuestionRec["tq_id"]])) {
					$aTemp  = $aAnswerContact[$aContactRec["client_persons_id"]."_".$aLogQuestionRec["tq_id"]];
					$aAnswerStatistic[$aTemp["tqa_id"]]++;
					$aAnswerTotal ++;
					$sQuestion = $aTemp["tqa_name"];
					unset($aAnswerContact[$aContactRec["client_persons_id"]."_".$aLogQuestionRec["tq_id"]]);
				}

				$sCellDayAnswer .= '<Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $sQuestion . '</Data></Cell>';
			}

			$sCellQuestionFree = "";
			foreach ($aQuestionFree as $aQuestionFreeRec) {
				$sAnswer = "";
				foreach ($aAnswerFree as $keyAnswer=> $aAnswerFreeRec) {

					if (($aAnswerFreeRec["client_persons_id"] == $aContactRec["client_persons_id"]) && ($aAnswerFreeRec["tqf_id"] == $aQuestionFreeRec["tqf_id"])) {
						$sAnswer = $aAnswerFreeRec["taf_answer"];
						unset($aAnswerFreeRec[$keyAnswer]);
					}
				}
				$sCellQuestionFree .= '<Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $sAnswer . '</Data></Cell>';
			}



			$sCellDayStatus = "";
			foreach ($aLogDays as $aLogDaysRec) {
				$sStatus = "";

				if(isset($aLogStatusContact[$aContactRec["client_persons_id"]."_".$aLogDaysRec["date_log"]])) {
					$aTemp  = $aLogStatusContact[$aContactRec["client_persons_id"]."_".$aLogDaysRec["date_log"]];
					$sStatus = $aTemp["ts_name"];
					unset($aLogStatusContact[$aContactRec["client_persons_id"]."_".$aLogDaysRec["date_log"]]);
				}
				$sCellDayStatus .= '<Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $sStatus . '</Data></Cell>';
			}


			$sCellCustom = "";
			foreach ($aCustom as $aCustomRec) {
				$sAnswer = "";
				foreach ($aCustomAnswer as $keyCustom=> $aCustomAnswerRec) {

					if (($aCustomAnswerRec["client_persons_id"] == $aContactRec["client_persons_id"]) && ($aCustomRec["tc_id"] == $aCustomAnswerRec["tc_id"])) {
						$sAnswer = $aCustomAnswerRec["tca_answer"];
						unset($aCustomAnswer[$keyCustom]);
					}
				}
				$sCellCustom .= '<Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $sAnswer . '</Data></Cell>';
			}

			// log_message("error",print_r ($aContactRec["tele_obs"],true));

			$newexcel->write_row('
                <Row  ss:Height="18">
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . ($key + 1) . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["company"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["lname"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["fname"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["position_name"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["city_id_val"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["county_id_val"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["ts_name"] . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . str_replace(array("<",">")," ",$aContactRec["tele_obs"]) . '</Data></Cell>
                    ' . $sCellCustom.$sCellDayAnswer . $sCellQuestionFree.$sCellDayStatus .   '
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ? $aContactRec["company"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ? $aContactRec["lname"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ? $aContactRec["fname"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$customs["sex"][$aContactRec["sex"]] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["domain_name"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["subdomain_name"]: "") . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["position_name"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["subposition_name"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["division_name"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["address"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["address2"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["city_id_val"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["county_id_val"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["phone"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["mobil"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["email"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["email2"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["web"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["birth"]: "") . '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$customs["age"][$aContactRec["age"]]: ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$customs["income"][$aContactRec["income"]] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["turnover"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$customs["employees"][$aContactRec["employees"]] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aContactRec["ts_contact_data"] ==1) ?$aContactRec["cui"] : ""). '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["user_name"]. '</Data></Cell>
                    <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aContactRec["person_id"]. '</Data></Cell>



                </Row>');
		}

		//afisare rapunsuri statistica
		$sSql = " select tele_question.*, tqa_id, tqa_name
                from tele_question_answer
                inner join tele_question on tele_question.tq_id=tele_question_answer.tq_id
                where client_id = {$iClient}
                order by tq_name asc, tqa_name  ";
		$aQuestion = $this->db->query($sSql)->result_array();

		foreach ($aQuestion as $key => $aQuestionRec) {
			$newexcel->write_row('
                 <Row  ss:Height="20">
                    <Cell ss:StyleID="teleLogChestionarTxt" ss:MergeAcross = "6"><Data ss:Type="String"></Data></Cell>
                    <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . (($aQuestion[$key - 1]["tq_id"] != $aQuestion[$key]["tq_id"]) ? $aQuestionRec["tq_name"] : "") . '</Data></Cell>
                    <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aQuestionRec["tqa_name"] . '</Data></Cell>
                     <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . $aAnswerStatistic[$aQuestionRec["tqa_id"]] . '</Data></Cell>
                     <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">' . round($iAnswerStatistic/$aAnswerTotal*100,2) . '</Data></Cell>
                 </Row>');
		}

		$newexcel->end_worksheet();
		return $newexcel->ws . $newexcel->rows . $newexcel->we;
	}

   function getLogByDay($iClient, $aLogDays) {


	    $customs["age"] = $this->config->item("ageContact");
        $customs["income"] = $this->config->item("incomeContact");
        $customs["employees"] = $this->config->item("employeeContact");
        $customs["available"] = $this->config->item("availableContact");
        $customs["priority"] = $this->config->item("priorityContact");
        $customs["sex"] = $this->config->item("sexContact");
        $customs["turnover"] = $this->config->item("turnoverContact") ;
        $this->db->where("ts_active", 1);
        $this->db->where("ts_interest", 1);
        //$this->db->where("ts_prog", 0);
        $this->db->where("client_id", $iClient);
        $aStatusInterest = $this->db->get("tele_status")->result_array();


        $newexcel = new Xmlexcel;
        $newexcel->start_worksheet("Interesati");
		
		 for ($i = 0; $i <= 100; $i++) {
            $sColumn .= '<Column ss:Width="150"/>';
        }
        $newexcel->write_row($sColumn);
		

		foreach ($aLogDays as $key => $aLogDayRec) {	   

			$aLogDay = $aLogDayRec["date_log"];
        
        $newexcel->write_row(' <Row ss:Height="50">       
        <Cell ss:StyleID="teleLogChestionarTxt" 	ss:MergeAcross = "15" ><Data ss:Type="String"></Data></Cell> </Row>');
        $newexcel->write_row(' <Row ss:Height="30"><Cell ss:StyleID="teleLogDayTitle" 	ss:MergeAcross = "15" ><Data ss:Type="String"> Telemarketing ' . date("d.m.Y", strtotime($aLogDay)) . '</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitle" 	ss:MergeAcross = "15" ><Data ss:Type="String"></Data></Cell> </Row>');
        $newexcel->write_row('<Row ss:Height="20"><Cell ss:StyleID="teleLogDayTitleColumn" 	ss:MergeAcross = "15" ><Data ss:Type="String"> Date de contact</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitle" 	ss:MergeAcross = "15" ><Data ss:Type="String"></Data></Cell> </Row>');

        $newexcel->write_row('
         <Row  ss:Height="20">
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nr Crt</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Compania</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nume</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Prenume</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Sex</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Functia</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Oras</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Judet</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Observatii</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Observati apel 1</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Observatii apel 2</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Observatii apel 3</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Observatii apel 4</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Domeniu</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Subdomeniu</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Functia</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Subfunctia</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Departament</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Adresa</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Cod postal</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Telefon</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Mobil</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Email</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Email 2</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Web</Data></Cell>           
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Grupa varsta</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Grupa venit</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Cifra afaceri</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Nr angajati</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Cod inregistrare</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">User</Data></Cell>
            <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">ID Baza Date</Data></Cell>			
         </Row>');

	    $i = 1;
        foreach ($aStatusInterest as $aRecInterest) {
            $sSQl = "  SELECT DATE(tl_date) as date_log, tele_log.client_persons_id,  tele_status.ts_name, tele_obs, tele_obs_apel_1,tele_obs_apel_2,tele_obs_apel_3,tele_obs_apel_4,
            division.name as division_name, persons.address, persons.address2, age, income, turnover, employees, cui,  user.name as user_name,  client_persons.person_id,
            IF(extern =1 , client_persons.company , persons.company) as company,
            IF(extern =1 , client_persons.lname , persons.lname) as lname,
            IF(extern =1 , client_persons.fname , persons.fname) as fname,
            IF(extern =1 , client_persons.sex , persons.sex) as sex,
            IF(extern =1 , CP.name , P.name) as position_name,
            IF(extern =1 , CSP.name , SP.name) as subposition_name,
            IF(extern =0 , persons.phone , client_persons.phone) as phone,
            IF(extern =0 , persons.mobil , client_persons.mobil) as mobil,
            IF(extern =1 , client_persons.email , persons.email) as email,
            IF(extern =1 , client_persons.email2 , persons.email2) as email2,
            IF(extern =1 , CC.name , city.name) as city_id_val,
            IF(extern =1 , client_persons.city_id , persons.city_id) as city_id,
            IF(extern =1 , CCo.name , county.name) as county_id_val,
            tele_status.ts_contact_data, `domain`.name as domain_name,   `SD`.name as subdomain_name
            FROM tele_log
            INNER JOIN (
                            SELECT max(tl_id) as tl_id
                            FROM tele_log
                            INNER JOIN client_persons on client_persons.client_persons_id=tele_log.client_persons_id and client_id={$iClient} and tele_log.tl_operation = 'status_mod' and
                                                DATE(tl_date) = '{$aLogDay}' and tl_value = {$aRecInterest["ts_id"]}
                            INNER JOIN tele_status on tele_status.ts_id = tl_value and ts_active = 1
                            group by tele_log.client_persons_id
            ) T on T.tl_id = tele_log.tl_id
            INNER JOIN tele_status on tele_status.ts_id = tele_log.tl_value
            left join client_persons on client_persons.client_persons_id = tele_log.client_persons_id
            left join persons on persons.person_id = client_persons.person_id
            left join position P on P.position_id=persons.position_id
			left join position CP on CP.position_id=client_persons.position_id
			left join position SP on SP.position_id=persons.position_sub_id
			left join position CSP on CSP.position_id=client_persons.position_sub_id
            left join city  on city.city_id=persons.city_id
            left join county  on county.county_id=persons.county_id
            left join city CC  on CC.city_id=client_persons.city_id
            left join county CCo on CCo.county_id=client_persons.county_id
            left join `domain`  on `domain`.domain_id=persons.domain_id
            left join `domain` SD  on `SD`.domain_id=persons.domain_sub_id
            left join `division`   on `division`.division_id=persons.division_id
            left join `user`   on `user`.user_id=client_persons.tele_user
            order by company asc ";

            $sql = $this->db->query($sSQl);

            if ($sql->num_rows() > 0) {            					            		
            	
                $newexcel->write_row('<Row><Cell ss:StyleID="teleLogDayTitleStatus" 	ss:MergeAcross = "15" ><Data ss:Type="String">' . $aRecInterest["ts_name"] . '</Data></Cell> </Row>');
                $aResult = $sql->result_array();
                foreach ($aResult as $aRecResult) {
                    $newexcel->write_row('<Row>
                        <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $i++ . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aRecResult["company"] . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aRecResult["lname"] . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aRecResult["fname"] . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $customs["sex"][$aRecResult["sex"]] . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aRecResult["position_name"] . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aRecResult["city_id_val"] . '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . $aRecResult["county_id_val"] . '</Data></Cell>
                        <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace(array("<",">")," ",$aRecResult["tele_obs"]) . '</Data></Cell>
                        <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aRecResult["tele_obs_apel_1"] . '</Data></Cell>
                        <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aRecResult["tele_obs_apel_2"] . '</Data></Cell>
                        <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aRecResult["tele_obs_apel_3"] . '</Data></Cell>
						<Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aRecResult["tele_obs_apel_4"] . '</Data></Cell>						
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["domain_name"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["subdomain_name"]: "") . '</Data></Cell>
                         <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' .(($aRecResult["ts_contact_data"] ==1) ?$aRecResult["position_name"]: ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["subposition_name"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["division_name"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["address"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["address2"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["phone"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["mobil"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["email"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["email2"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["web"] : ""). '</Data></Cell>
                     
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$customs["age"][$aRecResult["age"]]: ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$customs["income"][$aRecResult["income"]] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["turnover"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$customs["employees"][$aRecResult["employees"]] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' . (($aRecResult["ts_contact_data"] ==1) ?$aRecResult["cui"] : ""). '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' .  $aRecResult["user_name"]. '</Data></Cell>
                        <Cell ss:StyleID="teleLogChestionarTxt"><Data ss:Type="String">' .  $aRecResult["person_id"]. '</Data></Cell>  
                        </Row>');
                }
            }
        }

}

        $newexcel->end_worksheet();
        return $newexcel->ws . $newexcel->rows . $newexcel->we;
    }


    /**
     * Functie care aduce datele in grid
     */
    function exportSched($iClientId) {

        $sSql = "
        SELECT YEAR(tsched_date) as year ,WEEK(tsched_date) as week, DATE(tsched_date) as data,  position.name,tele_schedule.*
        FROM tele_schedule
        LEFT JOIN client_persons  on client_persons.client_persons_id =  tele_schedule.client_persons_id
        LEFT JOIN persons  on persons.person_id =  client_persons.person_id
        LEFT JOIN position  on position.position_id =  tele_schedule.position_id
        WHERE  client_persons.client_id =  {$iClientId}
        GROUP BY WEEK(tsched_date), tsched_id
        ORDER BY tsched_date      ";
        $aResult = $this->db->query($sSql)->result_array();

        $newexcel = new Xmlexcel;
        $newexcel->start_worksheet("Programari");

        $newexcel->write_row('
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            <Column ss:Width="150"/>
            ');

        $newexcel->write_row('
         <Row ss:Height= "20">
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Saptamana</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Data Intalnirii</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Ora stabilita pentru intalnire</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Loc intalnire</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Persoana contact</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Functia</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Firma</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Adresa firma</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Telefon</Data></Cell>
        <Cell ss:StyleID="teleLogDayTitleColumn"><Data ss:Type="String">Email</Data></Cell>
         </Row>');

        foreach ($aResult as $key => $row) {

            $aWeekTxt = "";
            $aWeek = weekToDays($row["week"], $row["year"]);
            if ($aResult[$key - 1]["week"] != $aResult[$key]["week"]) {
                $aWeekTxt = 'Saptamana ' . $aWeek[0] . " - " . $aWeek[6];
            }

            $sDayTxt = "";
            if ($aResult[$key - 1]["data"] != $aResult[$key]["data"]) {
                $sDayTxt = strTitle(strftime("%A", strtotime($row["data"]))) . " " . date('d.m', strtotime($row["data"]));
            }

            $newexcel->write_row('<Row>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aWeekTxt . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $sDayTxt . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . date("H:i", strtotime($row["tsched_date"])) . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["tsched_place"] . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["tsched_name"] . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["name"] . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["tsched_company"] . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["tsched_company_adr"] . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["tsched_phone"] . '</Data></Cell>
                <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["tsched_email"] . '</Data></Cell>
                </Row>');
        }

        $newexcel->end_worksheet();
        return $newexcel->ws . $newexcel->rows . $newexcel->we;
    }


    /**
     * Functie ce returneaza din log , dintr-o zi data, numarul de contacte cu un anumit status
     * @param  $aData
     * @param  $sData
     * @param  $iStatus
     * @return int
     */
    function getStatusZi($aData, $sData, $iStatus) {
        foreach ($aData as $aRecData) {
            if ($aRecData["date_log"] != $sData) {
                continue;
            }
            if ($aRecData["ts_id"] == $iStatus) {
                return $aRecData["cnt"];
            }
        }
        return 0;
    }

}
